My friends living in Cologne, Germany wish to relocate for professional reasons to Berlin, Germany. They reached out to me for recommendations on the Berlin suburbs. Rental prices are not their priority. They mentioned metrics like:
They know such a decision is very subjective but since I have also lived in Cologne and I know the Cologne suburbs quite a bit, they asked me if I could make a mapping of Berlin-to-Colone suburbs. That way, since they are familiar with the Cologne suburbs, they can get a first impression idea of the Berlin suburbs and reach easier a decision more tailored to their needs.
In order to tackle such a problem, I collected the following data:
Official names of the Berlin and Cologne suburbs were web-scraped. Representative nodes of the suburbs were queried from the Open Street Maps API. Data were corrected to reflect the officially recognized suburbs and their names. At the end of this data extraction and transformation, I had all officially recognized suburbs of Berlin and Cologne represented as nodes in WGS (latitude/longitute) coordinates.
The Foursquare API was queried around each representative suburb node using a radius that was determined separately for Berlin and Cologne. I wanted to capture enough of each suburb character allowing for query overlaps from neighboring suburbs in order to blend the suburb boundaries. This is because the reality on the ground is not influenced in any way by the administrative boundaries. A neighborhood can evolve across suburb boundaries and maintain its character. In such case, if the majority of the neighborhood lies only on one side of the boundary the similarity with the neighboring suburb will be missed if we do not allow for query blendings.
For the coordinate system transformation (CRS) from the World Geodesic System (WGS) latitude/longitude to Universal Transverse Mercador (UTM) cartesian I used EPSG:5243 which is appropriate for Germany.
Once the correct list of suburbs was acquired and geolocalized, we visualized the results with folium for final inspection. The next task was to determine the Foursquare query radius to use for each city. For that, I identified the nearest-neighbor of each suburb and computed the corresponding Euclidean distance. I then computed percentile statistics and visualized the distribution of distances for each city separately for inspection. In the end, I decided for radii in the range of the lower 10th percentile of nearest-neighbor suburb distances in each city.
I queried Foursquare for food, shop and services venues separately in each case and combined the results for each suburb. I kept all venues that were commonly found in Berlin and Cologne. For each city I removed outlier suburbs in the lower quartile of venue numbers. I then added the venue frequencies across the two cities and identified and removed from the analysis outlier venues that were in the lower quartile range.
Our goal was to make of map of Cologne suburbs to Berlin suburbs by minimizing their dissimilarity based on their number and type of venues. I quantified suburb dissimilarity by the normalized Euclidean distance in feature space. The normalization was just the number of features entering the analysis in order to make the dissimilarity measure invariant to changes in this number.
In order to make it easy for my friends, I reported the top two similar suburbs of Berlin for each of the suburbs of Cologne. Also, for each pair I reported the top similar and top dissimilar features these two suburbs had.
I identified all 96 of the Berlin suburbs and all 86 of the Cologne suburbs using the Open Street Maps API. Below I show snapshots of the city maps indicating the representative node for each suburb.
The next task was to decide on the Foursquare query radius (in meters) around the representative points of the suburbs for each city separately. For that, I converted the latitude/longitude coordinates to UTM and computed the Euclidean distance of each suburb to its nearest-neighbor. I show below a visualization of these nearest-neighboring distances for the suburbs of the two cities, where the zoom level might be different in each case.
The distributions of the nearest-neighbor distances for the two cities are visualized below. In the end, I decided the query radius to be close to the first quartile of nearest-neighbor distances for each city, namely for Berlin I used 1400 meters and for Cologne 1000 meters.
Food, shops and services queries to Foursquare were done separately. I consulted the general categories provided by Foursquare and decided to use the following broad categories:
| category | categoryId |
|---|---|
| food | 4d4b7105d754a06374d81259 |
| shop & service | 4d4b7105d754a06378d81259 |
I identified and kept for the analysis only the common venues between the two cities. As it was expected, some remote suburbs of Berlin and Cologne did not turn up many venues. I show below the distribution of the number of venues per suburb for each city. In the end, I decided to remove suburbs in the bottom 25%. I was sure my friends would not be interested in those anyway.
Finally, I pooled the venue frequencies across all suburbs of one city with those of the other city to get a final estimate of freature frequency in my dataset and I removed for further analysis features in the bottom 25% range of frequencies.
At this point I was ready for the main analysis. I standardized the features across suburbs in order to avoid the most populous, like Supermarkets dominating the analysis. Then, for each suburb of Cologne present in the analysis I computed its normalized Euclidean distance in feature space to all the Berlin suburbs present in the analysis. This resulted in a dissimilarity metric that was invariant to the number of features entering the analysis. Its range could vary. Values very close to zero indicated strongly similar suburbs. Values in the range of [0.1, 0.2] indicated somewhat similar suburbs and the rest indicated dissimilar suburbs.
For each suburb of Cologne I picked the two most similar suburbs of Berlin, i.e. those that had the smallest dissimilarity metric. I also identified the top features where the suburbs were most similar and most dissimilar and included them in the final table map. In the table below, I show the final map of Cologne to Berlin suburbs that this analysis produced.
# Behold the map of Cologne to Berlin suburbs
pd.set_option('display.max_rows', None)
final
| dissimilarity | top similar features | top dissimilar features | ||
|---|---|---|---|---|
| Cologne suburb | Berlin suburb | |||
| Altstadt-Nord | Steglitz | 0.173420 | Boutique, Lebanese Restaurant, Gastropub, Gift Shop, Fast Food Restaurant, French Restaurant | Locksmith, Thai Restaurant, Korean Restaurant, Smoke Shop, Seafood Restaurant, Lottery Retailer |
| Tiergarten | 0.185759 | Women's Store, Lebanese Restaurant, Sushi Restaurant, Bookstore, Paper / Office Supplies Store, Japanese Restaurant | Adult Boutique, Seafood Restaurant, Music Store, Food Truck, Kebab Restaurant, Persian Restaurant | |
| Altstadt-Süd | Charlottenburg | 0.153988 | Mexican Restaurant, Men's Store, Bagel Shop, Jewelry Store, Indian Restaurant, Middle Eastern Restaurant | Halal Restaurant, Laundromat, Doner Restaurant, Cosmetics Shop, Kebab Restaurant, Taverna |
| Steglitz | 0.167122 | Men's Store, Persian Restaurant, Mexican Restaurant, Vietnamese Restaurant, Bagel Shop, Farmers Market | Irish Pub, Currywurst Joint, Locksmith, Smoke Shop, Sushi Restaurant, Lottery Retailer | |
| Bayenthal | Heinersdorf | 0.108275 | Astrologer, Deli / Bodega, Organic Grocery, Drugstore, Market, Cosmetics Shop | Currywurst Joint, Discount Store, Motorcycle Shop, Fried Chicken Joint, Fast Food Restaurant, Health & Beauty Service |
| Waidmannslust | 0.108364 | Fish Market, Astrologer, Deli / Bodega, Organic Grocery, Market, Cosmetics Shop | Business Service, Medical Supply Store, Health & Beauty Service, Gastropub, Liquor Store, Big Box Store | |
| Bickendorf | Weißensee | 0.090279 | Entertainment Service, Market, Rental Service, Wine Shop, Photography Studio, Massage Studio | Film Studio, Diner, Lottery Retailer, Print Shop, Mobile Phone Shop, Bakery |
| Britz | 0.098458 | Entertainment Service, Astrologer, Home Service, Flower Shop, Rental Service, Wine Shop | IT Services, Garden Center, Pet Store, Chinese Restaurant, Furniture / Home Store, ATM | |
| Bilderstöckchen | Biesdorf | 0.048144 | Market, Arts & Crafts Store, Business Service, Pharmacy, Electronics Store, Bakery | Discount Store, Home Service, Construction & Landscaping, Big Box Store, Pet Store, Supermarket |
| Dahlem | 0.052537 | Arts & Crafts Store, Business Service, Pharmacy, Shipping Store, Shopping Mall, Drugstore | Garden Center, Lawyer, German Restaurant, Asian Restaurant, IT Services, Café | |
| Bocklemünd/Mengenich | Dahlem | 0.060193 | ATM, Food Truck, Eastern European Restaurant, Fast Food Restaurant, Snack Place, Pharmacy | Garden Center, Market, Lawyer, Asian Restaurant, IT Services, Café |
| Bohnsdorf | 0.060864 | ATM, Fast Food Restaurant, Eastern European Restaurant, Food & Drink Shop, Snack Place, Pharmacy | Discount Store, Food Truck, Insurance Office, Home Service, Flower Shop, Shopping Mall | |
| Braunsfeld | Kaulsdorf | 0.100739 | Entertainment Service, Lawyer, Film Studio, Event Service, Food, Diner | Medical Supply Store, Home Service, Gastropub, Liquor Store, Pet Store, Construction & Landscaping |
| Marienfelde | 0.105325 | Entertainment Service, Lawyer, Film Studio, Event Service, Food, Market | Lottery Retailer, Business Service, Health & Beauty Service, Farmers Market, Big Box Store, Chinese Restaurant | |
| Brück | Biesdorf | 0.041889 | Thai Restaurant, Paper / Office Supplies Store, Trattoria/Osteria, Bakery, German Restaurant, Drugstore | Discount Store, Home Service, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store |
| Friedrichshagen | 0.045860 | Thai Restaurant, Paper / Office Supplies Store, Trattoria/Osteria, Supermarket, Drugstore, ATM | Flea Market, Mobile Phone Shop, Mexican Restaurant, Flower Shop, Snack Place, Asian Restaurant | |
| Buchforst | Lichtenrade | 0.097590 | Fruit & Vegetable Store, Grocery Store, Fast Food Restaurant, Cosmetics Shop, Smoke Shop, Pizza Place | Locksmith, Arts & Crafts Store, Medical Supply Store, Mexican Restaurant, Jewelry Store, Insurance Office |
| Hermsdorf | 0.098277 | Fruit & Vegetable Store, Grocery Store, Department Store, Photography Studio, Cosmetics Shop, Smoke Shop | Mexican Restaurant, Health & Beauty Service, Wine Shop, Home Service, Drugstore, Snack Place | |
| Buchheim | Biesdorf | 0.055712 | Food Truck, Seafood Restaurant, Photography Studio, Business Service, Department Store, IT Services | Discount Store, Shipping Store, Construction & Landscaping, Pet Store, Doner Restaurant, Pharmacy |
| Hermsdorf | 0.056919 | Photography Studio, Food Truck, Big Box Store, Department Store, Seafood Restaurant, Gastropub | Construction & Landscaping, Mexican Restaurant, Health & Beauty Service, Wine Shop, Snack Place, Liquor Store | |
| Chorweiler | Marzahn | 0.067567 | Thai Restaurant, Smoke Shop, Liquor Store, Toy / Game Store, Lottery Retailer, BBQ Joint | Auto Garage, Medical Supply Store, German Restaurant, Health & Beauty Service, Flower Shop, Sandwich Place |
| Buckow | 0.070244 | Thai Restaurant, Smoke Shop, Lottery Retailer, BBQ Joint, Electronics Store, Construction & Landscaping | Kids Store, Big Box Store, Gift Shop, Discount Store, Pet Store, Business Service | |
| Dellbrück | Waidmannslust | 0.057571 | Furniture / Home Store, Lottery Retailer, Farmers Market, Department Store, Pet Store, Shoe Store | Other Repair Shop, Electronics Store, Medical Supply Store, Business Service, Gastropub, Liquor Store |
| Hermsdorf | 0.059366 | Pet Store, Furniture / Home Store, Lottery Retailer, Farmers Market, Department Store, Shoe Store | Mexican Restaurant, Wine Shop, Snack Place, Seafood Restaurant, Electronics Store, Liquor Store | |
| Deutz | Charlottenburg-Nord | 0.146693 | Medical Supply Store, Rental Service, Sandwich Place, Cafeteria, Bistro, Eastern European Restaurant | Smoke Shop, Gift Shop, Shopping Mall, Persian Restaurant, Electronics Store, Construction & Landscaping |
| Siemensstadt | 0.146733 | Rental Service, Sandwich Place, Cafeteria, Bistro, Eastern European Restaurant, Breakfast Spot | Print Shop, Furniture / Home Store, Business Service, IT Services, Thai Restaurant, Big Box Store | |
| Dünnwald | Köpenick | 0.040688 | Print Shop, Snack Place, Shopping Mall, Supermarket, Pizza Place, Locksmith | Seafood Restaurant, Gastropub, IT Services, Sporting Goods Shop, German Restaurant, Japanese Restaurant |
| Lichtenrade | 0.050522 | Print Shop, Snack Place, Greek Restaurant, Pizza Place, Locksmith, ATM | Arts & Crafts Store, Department Store, Business Service, Medical Supply Store, Mexican Restaurant, Jewelry Store | |
| Ehrenfeld | Schöneberg | 0.176305 | Beer Store, Medical Supply Store, Vegetarian / Vegan Restaurant, Kebab Restaurant, Locksmith, Falafel Restaurant | Astrologer, Kids Store, French Restaurant, Paper / Office Supplies Store, Laundromat, Snack Place |
| Neukölln | 0.176896 | Beer Store, Medical Supply Store, Kebab Restaurant, Taverna, Locksmith, Print Shop | Breakfast Spot, Middle Eastern Restaurant, Spanish Restaurant, French Restaurant, Austrian Restaurant, Israeli Restaurant | |
| Ensen | Hermsdorf | 0.065884 | Astrologer, Flower Shop, Big Box Store, Health & Beauty Service, Restaurant, Optical Shop | Construction & Landscaping, Mexican Restaurant, Wine Shop, Home Service, Drugstore, Snack Place |
| Biesdorf | 0.066434 | Astrologer, Health & Beauty Service, Flower Shop, ATM, Business Service, Restaurant | Discount Store, Home Service, Shipping Store, Shopping Mall, Construction & Landscaping, Pet Store | |
| Finkenberg | Britz | 0.065294 | Shipping Store, Asian Restaurant, Construction & Landscaping, Home Service, Grocery Store, Food Service | IT Services, Garden Center, Market, Furniture / Home Store, ATM, Eastern European Restaurant |
| Biesdorf | 0.067811 | Food Service, Auto Garage, Shipping Store, Chinese Restaurant, Food & Drink Shop, Construction & Landscaping | Discount Store, Shopping Mall, Furniture / Home Store, Doner Restaurant, Mobile Phone Shop, Insurance Office | |
| Fühlingen | Biesdorf | 0.043208 | Rental Service, Electronics Store, Greek Restaurant, Business Service, Turkish Restaurant, Bakery | Discount Store, Home Service, Shipping Store, Supermarket, Big Box Store, Pet Store |
| Hermsdorf | 0.045928 | Rental Service, Bakery, Electronics Store, Shopping Mall, Turkish Restaurant, Greek Restaurant | Mexican Restaurant, Business Service, Health & Beauty Service, Wine Shop, Home Service, Drugstore | |
| Gremberghoven | Biesdorf | 0.062076 | Laundromat, Business Service, Snack Place, Fast Food Restaurant, IT Services, Men's Store | Discount Store, Home Service, Supermarket, Shopping Mall, Construction & Landscaping, Big Box Store |
| Hermsdorf | 0.062517 | Laundromat, Shipping Store, Men's Store, Burger Joint, Fast Food Restaurant, Turkish Restaurant | Construction & Landscaping, Mexican Restaurant, Health & Beauty Service, Wine Shop, Home Service, Drugstore | |
| Hahnwald | Kaulsdorf | 0.063839 | Print Shop, Flower Shop, Shopping Mall, Business Service, Asian Restaurant, BBQ Joint | Construction & Landscaping, Market, Motorcycle Shop, Medical Supply Store, Gastropub, Food & Drink Shop |
| Biesdorf | 0.064648 | Print Shop, Flower Shop, Diner, Liquor Store, Business Service, Shopping Mall | Discount Store, Shipping Store, Supermarket, Construction & Landscaping, Big Box Store, Pet Store | |
| Heimersdorf | Dahlem | 0.051657 | Film Studio, Garden Center, Sporting Goods Shop, Drugstore, Turkish Restaurant, Furniture / Home Store | Market, Asian Restaurant, Café, German Restaurant, Fast Food Restaurant, Bookstore |
| Lankwitz | 0.064615 | Film Studio, Sporting Goods Shop, Lawyer, Food & Drink Shop, Turkish Restaurant, Mexican Restaurant | Auto Garage, Optical Shop, Department Store, ATM, Insurance Office, Fast Food Restaurant | |
| Holweide | Hermsdorf | 0.057801 | Gastropub, Lottery Retailer, Department Store, Health & Beauty Service, Mediterranean Restaurant, Breakfast Spot | Business Service, Construction & Landscaping, Mexican Restaurant, Wine Shop, Snack Place, Seafood Restaurant |
| Biesdorf | 0.057874 | Health & Beauty Service, Gastropub, Lottery Retailer, Department Store, Mediterranean Restaurant, Breakfast Spot | Discount Store, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store, Pet Store | |
| Humboldt/Gremberg | Mahlsdorf | 0.070167 | Big Box Store, Liquor Store, Auto Workshop, Organic Grocery, Paper / Office Supplies Store, Men's Store | Construction & Landscaping, Garden Center, Motorcycle Shop, Furniture / Home Store, Business Service, Home Service |
| Rosenthal | 0.074589 | Big Box Store, Auto Workshop, Liquor Store, Organic Grocery, Paper / Office Supplies Store, Cosmetics Shop | Auto Garage, Mexican Restaurant, Shipping Store, Steakhouse, Food & Drink Shop, Trattoria/Osteria | |
| Höhenberg | Biesdorf | 0.049129 | Smoke Shop, Motorcycle Shop, Lottery Retailer, Shipping Store, Fast Food Restaurant, Bookstore | Discount Store, Big Box Store, Pet Store, Supermarket, Furniture / Home Store, Doner Restaurant |
| Kaulsdorf | 0.057311 | Shipping Store, Smoke Shop, Lottery Retailer, Bookstore, Convenience Store, Pizza Place | Diner, Market, Medical Supply Store, Furniture / Home Store, Gastropub, Electronics Store | |
| Höhenhaus | Biesdorf | 0.042695 | Motorcycle Shop, Cosmetics Shop, BBQ Joint, Pharmacy, Greek Restaurant, Café | Discount Store, Home Service, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store |
| Kaulsdorf | 0.047671 | BBQ Joint, Pharmacy, Greek Restaurant, Café, Cosmetics Shop, Supermarket | Diner, Construction & Landscaping, Market, Medical Supply Store, Home Service, Furniture / Home Store | |
| Junkersdorf | Biesdorf | 0.090196 | Fried Chicken Joint, Furniture / Home Store, Bistro, Shoe Store, Toy / Game Store, Gourmet Shop | Discount Store, Home Service, Shipping Store, Doner Restaurant, Asian Restaurant, Mobile Phone Shop |
| Staaken | 0.091097 | Fried Chicken Joint, Furniture / Home Store, Bistro, Big Box Store, Shoe Store, Gourmet Shop | Auto Garage, Eastern European Restaurant, Trattoria/Osteria, Turkish Restaurant, Toy / Game Store, Cosmetics Shop | |
| Kalk | Buckow | 0.150866 | Halal Restaurant, Currywurst Joint, Fruit & Vegetable Store, Sporting Goods Shop, Bagel Shop, Discount Store | Health & Beauty Service, Toy / Game Store, Steakhouse, German Restaurant, Greek Restaurant, Insurance Office |
| Neu-Hohenschönhausen | 0.160234 | Halal Restaurant, Sporting Goods Shop, Currywurst Joint, Fruit & Vegetable Store, Pharmacy, Bagel Shop | Home Service, Auto Garage, Women's Store, Smoke Shop, Chinese Restaurant, Burger Joint | |
| Klettenberg | Marienfelde | 0.053108 | Antique Shop, Food & Drink Shop, Construction & Landscaping, ATM, Café, German Restaurant | Lottery Retailer, Arts & Crafts Store, Fast Food Restaurant, Health & Beauty Service, IT Services, Photography Studio |
| Kaulsdorf | 0.054405 | Antique Shop, ATM, Farmers Market, Business Service, Café, Food & Drink Shop | Market, Motorcycle Shop, Medical Supply Store, Home Service, Furniture / Home Store, Gastropub | |
| Lind | Biesdorf | 0.039974 | Health & Beauty Service, Construction & Landscaping, Furniture / Home Store, Indian Restaurant, Restaurant, Convenience Store | Discount Store, Home Service, Supermarket, Shipping Store, Big Box Store, Pet Store |
| Kaulsdorf | 0.046497 | Health & Beauty Service, Indian Restaurant, Furniture / Home Store, Restaurant, Shopping Mall, Convenience Store | Diner, Market, Motorcycle Shop, Medical Supply Store, Home Service, Gastropub | |
| Lindenthal | Friedrichshagen | 0.105620 | Accessories Store, Kids Store, Deli / Bodega, Trattoria/Osteria, Antique Shop, Grocery Store | Flea Market, Mexican Restaurant, Flower Shop, Asian Restaurant, Mobile Phone Shop, Liquor Store |
| Dahlem | 0.107012 | Accessories Store, Trattoria/Osteria, Kids Store, Deli / Bodega, Antique Shop, Grocery Store | Garden Center, Asian Restaurant, IT Services, Food & Drink Shop, Lawyer, Fast Food Restaurant | |
| Lindweiler | Dahlem | 0.045880 | Steakhouse, Business Service, Optical Shop, Insurance Office, Greek Restaurant, Furniture / Home Store | Garden Center, Market, German Restaurant, Asian Restaurant, Café, Fast Food Restaurant |
| Friedrichshagen | 0.048092 | Steakhouse, Lawyer, IT Services, Business Service, Restaurant, Food & Drink Shop | Flea Market, Mobile Phone Shop, Mexican Restaurant, Flower Shop, Snack Place, Asian Restaurant | |
| Lövenich | Biesdorf | 0.045839 | Trattoria/Osteria, Gastropub, Business Service, German Restaurant, Bakery, Medical Supply Store | Discount Store, Home Service, Shipping Store, Shopping Mall, Big Box Store, Pet Store |
| Köpenick | 0.045948 | Trattoria/Osteria, Gastropub, Supermarket, Furniture / Home Store, Bakery, Flower Shop | Locksmith, Seafood Restaurant, IT Services, Sporting Goods Shop, Japanese Restaurant, Greek Restaurant | |
| Marienburg | Biesdorf | 0.046008 | Insurance Office, Trattoria/Osteria, Fast Food Restaurant, Grocery Store, IT Services, Business Service | Discount Store, Home Service, Supermarket, Shopping Mall, Construction & Landscaping, Big Box Store |
| Dahlem | 0.047493 | Insurance Office, Trattoria/Osteria, Shipping Store, Grocery Store, Business Service, Italian Restaurant | Garden Center, Market, Asian Restaurant, Café, Food & Drink Shop, Bakery | |
| Mauenheim | Biesdorf | 0.052189 | Pharmacy, Bakery, Event Service, Health & Beauty Service, Modern European Restaurant, German Restaurant | Discount Store, Home Service, Shipping Store, Construction & Landscaping, Big Box Store, Pet Store |
| Reinickendorf | 0.057787 | Pharmacy, Health & Beauty Service, Bakery, Shopping Mall, Modern European Restaurant, Doner Restaurant | Massage Studio, Construction & Landscaping, Discount Store, Trattoria/Osteria, Lawyer, Shipping Store | |
| Merheim | Biesdorf | 0.043614 | Lottery Retailer, Music Store, Health & Beauty Service, German Restaurant, Big Box Store, Electronics Store | Discount Store, Home Service, Shipping Store, Furniture / Home Store, Doner Restaurant, Pharmacy |
| Marienfelde | 0.049057 | Pet Store, Music Store, German Restaurant, Big Box Store, Shopping Mall, Flower Shop | Diner, Arts & Crafts Store, Business Service, Fast Food Restaurant, Farmers Market, IT Services | |
| Mülheim | Niederschönhausen | 0.110852 | Turkish Restaurant, Snack Place, Doner Restaurant, BBQ Joint, Grocery Store, Market | Tapas Restaurant, Mexican Restaurant, Lawyer, Farmers Market, Perfume Shop, Home Service |
| Lichterfelde | 0.112296 | Turkish Restaurant, Doner Restaurant, Grocery Store, Market, Snack Place, Seafood Restaurant | Home Service, Discount Store, Eastern European Restaurant, Construction & Landscaping, Liquor Store, Big Box Store | |
| Müngersdorf | Haselhorst | 0.075006 | American Restaurant, Notary, Liquor Store, Arts & Crafts Store, Spanish Restaurant, Mexican Restaurant | Beer Store, German Restaurant, IT Services, Construction & Landscaping, Electronics Store, Shoe Store |
| Wilhelmsruh | 0.084671 | American Restaurant, Notary, Big Box Store, Liquor Store, Sporting Goods Shop, Print Shop | Gift Shop, Garden Center, Modern European Restaurant, Shopping Mall, Pharmacy, Cosmetics Shop | |
| Neuehrenfeld | Rosenthal | 0.103359 | Smoke Shop, Notary, Business Service, Beer Store, Greek Restaurant, Construction & Landscaping | Auto Workshop, Mexican Restaurant, Steakhouse, Insurance Office, Photography Studio, Music Store |
| Niederschönhausen | 0.104789 | Smoke Shop, Auto Garage, Notary, Beer Store, Falafel Restaurant, Food & Drink Shop | Lawyer, Pharmacy, Photography Studio, Mexican Restaurant, Jewelry Store, Farmers Market | |
| Neustadt-Nord | Tiergarten | 0.199085 | Austrian Restaurant, Souvenir Shop, Convenience Store, Laundromat, Paper / Office Supplies Store, Japanese Restaurant | Music Store, Food Truck, Kebab Restaurant, Persian Restaurant, Currywurst Joint, American Restaurant |
| Neukölln | 0.200970 | Adult Boutique, Salad Place, Record Shop, Souvenir Shop, Massage Studio, Japanese Restaurant | Lebanese Restaurant, Bistro, Israeli Restaurant, Breakfast Spot, Falafel Restaurant, Middle Eastern Restaurant | |
| Neustadt-Süd | Friedenau | 0.194458 | Israeli Restaurant, Creperie, Middle Eastern Restaurant, Persian Restaurant, Kebab Restaurant, Other Repair Shop | Antique Shop, Astrologer, Women's Store, Eastern European Restaurant, Smoke Shop, Lottery Retailer |
| Charlottenburg | 0.195465 | Israeli Restaurant, Toy / Game Store, Creperie, Middle Eastern Restaurant, Irish Pub, Mattress Store | Gift Shop, Halal Restaurant, Asian Restaurant, Laundromat, Trattoria/Osteria, Sporting Goods Shop | |
| Nippes | Niederschönhausen | 0.095896 | Doner Restaurant, Flea Market, Beer Store, Thai Restaurant, Wine Shop, Kebab Restaurant | Lawyer, Photography Studio, Mexican Restaurant, Jewelry Store, Shoe Store, Toy / Game Store |
| Friedrichshagen | 0.105644 | Doner Restaurant, Supermarket, Thai Restaurant, Beer Store, Optical Shop, Italian Restaurant | Mexican Restaurant, Flower Shop, Snack Place, Asian Restaurant, Japanese Restaurant, Liquor Store | |
| Ossendorf | Biesdorf | 0.062260 | Furniture / Home Store, Bistro, Print Shop, Steakhouse, Electronics Store, Lawyer | Discount Store, Shipping Store, Supermarket, Construction & Landscaping, Big Box Store, Pet Store |
| Kaulsdorf | 0.063579 | Furniture / Home Store, Bistro, Print Shop, Steakhouse, Lawyer, Shopping Mall | Diner, Construction & Landscaping, Market, Motorcycle Shop, Medical Supply Store, Liquor Store | |
| Ostheim | Biesdorf | 0.031552 | Pizza Place, Doner Restaurant, Bakery, Medical Supply Store, ATM, Italian Restaurant | Discount Store, Home Service, Shipping Store, Shopping Mall, Big Box Store, Pet Store |
| Dahlem | 0.043025 | Supermarket, Construction & Landscaping, Doner Restaurant, Furniture / Home Store, Flower Shop, Medical Supply Store | Garden Center, Market, Lawyer, German Restaurant, Asian Restaurant, IT Services | |
| Pesch | Rosenthal | 0.044023 | Business Service, IT Services, Optical Shop, Electronics Store, Steakhouse, Supermarket | Auto Workshop, Mexican Restaurant, Event Service, Shipping Store, Food & Drink Shop, Trattoria/Osteria |
| Biesdorf | 0.047636 | Auto Garage, Steakhouse, Business Service, IT Services, Optical Shop, Greek Restaurant | Discount Store, Home Service, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store | |
| Poll | Hermsdorf | 0.068220 | Child Care Service, Big Box Store, Motorcycle Shop, Department Store, Paper / Office Supplies Store, Furniture / Home Store | Mexican Restaurant, Business Service, Wine Shop, Home Service, Seafood Restaurant, IT Services |
| Johannisthal | 0.068348 | Child Care Service, Big Box Store, Electronics Store, Motorcycle Shop, Department Store, Paper / Office Supplies Store | Home Service, Taverna, Print Shop, Event Service, Shipping Store, Health & Beauty Service | |
| Porz | Biesdorf | 0.074432 | Home Service, Lawyer, Mobile Phone Shop, Eastern European Restaurant, Arts & Crafts Store, Print Shop | Discount Store, Shipping Store, Big Box Store, Pet Store, Flower Shop, Photography Studio |
| Lichterfelde | 0.077129 | Mobile Phone Shop, Arts & Crafts Store, Asian Restaurant, Print Shop, Steakhouse, Massage Studio | Snack Place, Discount Store, Fast Food Restaurant, Jewelry Store, IT Services, Photography Studio | |
| Raderberg | Friedrichsfelde | 0.140985 | Fish Market, Arts & Crafts Store, Burger Joint, Other Repair Shop, Deli / Bodega, Flea Market | Health & Beauty Service, Snack Place, Gastropub, Discount Store, Fast Food Restaurant, Medical Supply Store |
| Wedding | 0.142589 | Cafeteria, Fish Market, Other Repair Shop, Flea Market, Music Store, Pet Store | Fruit & Vegetable Store, Entertainment Service, Accessories Store, Adult Boutique, Middle Eastern Restaurant, Taverna | |
| Rath/Heumar | Heiligensee | 0.039865 | Motorcycle Shop, Health & Beauty Service, Home Service, Fast Food Restaurant, Business Service, Furniture / Home Store | Insurance Office, IT Services, Greek Restaurant, Trattoria/Osteria, ATM, Bakery |
| Biesdorf | 0.055530 | Insurance Office, Motorcycle Shop, Health & Beauty Service, Fast Food Restaurant, Business Service, Café | Discount Store, Supermarket, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store | |
| Riehl | Friedrichshagen | 0.073348 | Event Service, Souvenir Shop, Rental Service, Wine Shop, Auto Garage, Pet Store | Mobile Phone Shop, Mexican Restaurant, Asian Restaurant, Japanese Restaurant, Construction & Landscaping, Food & Drink Shop |
| Hermsdorf | 0.079628 | Flea Market, Event Service, Souvenir Shop, Flower Shop, Rental Service, Auto Garage | Construction & Landscaping, Mexican Restaurant, Business Service, Home Service, Drugstore, Seafood Restaurant | |
| Rodenkirchen | Hermsdorf | 0.056761 | Wine Shop, Perfume Shop, Cosmetics Shop, Jewelry Store, Optical Shop, Bookstore | Business Service, Construction & Landscaping, Mexican Restaurant, Snack Place, Seafood Restaurant, IT Services |
| Biesdorf | 0.060596 | Wine Shop, Perfume Shop, Health & Beauty Service, Cosmetics Shop, Fast Food Restaurant, Drugstore | Discount Store, Shipping Store, Construction & Landscaping, Big Box Store, Pet Store, Furniture / Home Store | |
| Seeberg | Dahlem | 0.063305 | Film Studio, Garden Center, Toy / Game Store, Sporting Goods Shop, Thai Restaurant, BBQ Joint | Market, Lawyer, German Restaurant, Asian Restaurant, IT Services, Café |
| Staaken | 0.065373 | Garden Center, Film Studio, Thai Restaurant, BBQ Joint, Optical Shop, Shopping Mall | Auto Garage, Pet Store, Eastern European Restaurant, Construction & Landscaping, Trattoria/Osteria, Turkish Restaurant | |
| Stammheim | Biesdorf | 0.041148 | Lottery Retailer, Music Store, Gastropub, Business Service, Pizza Place, Restaurant | Discount Store, Home Service, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store |
| Dahlem | 0.046244 | Lottery Retailer, Music Store, Gastropub, Supermarket, Business Service, Flower Shop | Garden Center, Market, Lawyer, German Restaurant, Asian Restaurant, IT Services | |
| Sülz | Friedrichshagen | 0.064488 | Antique Shop, Sporting Goods Shop, ATM, Department Store, Paper / Office Supplies Store, Mediterranean Restaurant | Flea Market, Mobile Phone Shop, Mexican Restaurant, Flower Shop, Snack Place, Asian Restaurant |
| Köpenick | 0.065642 | Antique Shop, Bakery, Department Store, Paper / Office Supplies Store, Food & Drink Shop, Mediterranean Restaurant | Locksmith, Seafood Restaurant, Gastropub, IT Services, Japanese Restaurant, Breakfast Spot | |
| Sürth | Biesdorf | 0.056335 | IT Services, Greek Restaurant, German Restaurant, Lottery Retailer, Health & Beauty Service, Restaurant | Home Service, Shipping Store, Shopping Mall, Big Box Store, Pet Store, Supermarket |
| Hermsdorf | 0.056874 | Discount Store, IT Services, German Restaurant, Lottery Retailer, Greek Restaurant, Restaurant | Mexican Restaurant, Business Service, Wine Shop, Home Service, Drugstore, Snack Place | |
| Urbach | Alt-Hohenschönhausen | 0.047092 | Snack Place, IT Services, Pizza Place, Supermarket, Greek Restaurant, Electronics Store | Auto Garage, Flea Market, Mediterranean Restaurant, Health & Beauty Service, Shipping Store, Home Service |
| Biesdorf | 0.049277 | IT Services, Greek Restaurant, Arts & Crafts Store, Electronics Store, Snack Place, Discount Store | Shipping Store, Construction & Landscaping, Big Box Store, Pet Store, Furniture / Home Store, Doner Restaurant | |
| Vingst | Biesdorf | 0.055270 | Smoke Shop, Falafel Restaurant, Lottery Retailer, Event Service, Organic Grocery, Pizza Place | Discount Store, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store, Pet Store |
| Dahlem | 0.058789 | Smoke Shop, Falafel Restaurant, Lottery Retailer, Event Service, Organic Grocery, Home Service | Garden Center, Market, German Restaurant, Asian Restaurant, Café, Food & Drink Shop | |
| Vogelsang | Dahlem | 0.062467 | Entertainment Service, Liquor Store, Garden Center, Department Store, Photography Studio, Home Service | Market, Asian Restaurant, IT Services, Food & Drink Shop, German Restaurant, Lawyer |
| Märkisches Viertel | 0.063255 | Liquor Store, Garden Center, Grocery Store, Construction & Landscaping, Entertainment Service, Lawyer | American Restaurant, Auto Garage, Arts & Crafts Store, Gift Shop, Shopping Mall, Shoe Store | |
| Wahn | Johannisthal | 0.058853 | Child Care Service, Auto Garage, Chinese Restaurant, Snack Place, Drugstore, Gastropub | Health & Beauty Service, Taverna, Event Service, Shipping Store, Home Service, Liquor Store |
| Baumschulenweg | 0.064565 | Child Care Service, ATM, Chinese Restaurant, Snack Place, Photography Studio, Home Service | Locksmith, Garden Center, Mobile Phone Shop, Eastern European Restaurant, Cosmetics Shop, Shipping Store | |
| Wahnheide | Hermsdorf | 0.048877 | Auto Garage, Health & Beauty Service, Lawyer, Furniture / Home Store, German Restaurant, Italian Restaurant | Construction & Landscaping, Mexican Restaurant, Business Service, Wine Shop, Home Service, Drugstore |
| Rosenthal | 0.048937 | Health & Beauty Service, Fast Food Restaurant, Business Service, Lawyer, Furniture / Home Store, Italian Restaurant | Auto Workshop, Mexican Restaurant, Event Service, Shipping Store, Steakhouse, Food & Drink Shop | |
| Weiden | Gropiusstadt | 0.087122 | Photography Studio, Jewelry Store, Smoke Shop, Men's Store, Shopping Mall, Seafood Restaurant | Diner, Perfume Shop, Fast Food Restaurant, Mexican Restaurant, Women's Store, Farmers Market |
| Neu-Hohenschönhausen | 0.087289 | Clothing Store, Photography Studio, Jewelry Store, Men's Store, Gift Shop, Asian Restaurant | Home Service, Auto Garage, Discount Store, Business Service, Fast Food Restaurant, Women's Store | |
| Weidenpesch | Friedrichshagen | 0.058081 | Korean Restaurant, Arts & Crafts Store, Massage Studio, Flea Market, Health & Beauty Service, Supermarket | Mobile Phone Shop, Mexican Restaurant, Flower Shop, Snack Place, Asian Restaurant, Japanese Restaurant |
| Biesdorf | 0.061824 | Flea Market, Korean Restaurant, Arts & Crafts Store, Massage Studio, Health & Beauty Service, Jewelry Store | Discount Store, Home Service, Shipping Store, Shopping Mall, Big Box Store, Pet Store | |
| Widdersdorf | Bohnsdorf | 0.051256 | Locksmith, Gastropub, Business Service, Pizza Place, Food Truck, ATM | Discount Store, Insurance Office, Home Service, Flower Shop, Shopping Mall, Liquor Store |
| Köpenick | 0.053092 | Food Truck, Pizza Place, Locksmith, Italian Restaurant, Supermarket, Flower Shop | Seafood Restaurant, IT Services, Sporting Goods Shop, German Restaurant, Japanese Restaurant, Construction & Landscaping | |
| Worringen | Biesdorf | 0.070656 | Food, Taverna, Asian Restaurant, Burger Joint, Electronics Store, Pizza Place | Discount Store, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store, Pet Store |
| Nikolassee | 0.072561 | Food, Taverna, Home Service, Asian Restaurant, Burger Joint, Electronics Store | Fish Market, Bagel Shop, Fast Food Restaurant, Toy / Game Store, Snack Place, Bistro | |
| Zollstock | Staaken | 0.086649 | Mattress Store, Chinese Restaurant, Eastern European Restaurant, Pizza Place, Food Truck, German Restaurant | Toy / Game Store, Pet Store, Trattoria/Osteria, Turkish Restaurant, Fast Food Restaurant, Cosmetics Shop |
| Oberschöneweide | 0.088708 | Mattress Store, Eastern European Restaurant, Food Truck, Auto Garage, ATM, Music Store | Snack Place, Irish Pub, Doner Restaurant, Business Service, Sandwich Place, Gastropub | |
| Zündorf | Biesdorf | 0.046816 | Flower Shop, Lottery Retailer, Health & Beauty Service, French Restaurant, Pharmacy, Greek Restaurant | Discount Store, Shipping Store, Shopping Mall, Construction & Landscaping, Big Box Store, Pet Store |
| Friedrichshagen | 0.052634 | Lottery Retailer, Pharmacy, Flower Shop, Health & Beauty Service, Home Service, French Restaurant | Flea Market, Mobile Phone Shop, Mexican Restaurant, Snack Place, Asian Restaurant, Japanese Restaurant |
On the one hand, there were Cologne suburbs very close in character to the Berlin ones. On the other hand, there were also suburbs which seemed to be unique in their Cologne character and their closest counterpart in Berlin was quite dissimilar. This can also be seen by the distribution of the dissimilarity metric shown below.
I found the Cologne to Berlin suburb map very interesting. It turned out, the Cologne city center suburbs were very distant from all choices in Berlin and vice versa, no Berlin city center suburb was found similar with any of the Cologne suburbs. Citi centers seem to have a unique non-transferable character. Althought it should be said that a character of a suburb cannot be captured only by looking at food venues, shops and services. Nevertheless, this simple first approach to the problem gave my friends some broad-stroke ideas about the Berlin suburbs in order to get them going and help them narrowing down their choices.
On the Data Science side of things, I can see many areas where this analysis could be expanded and improved. Firstly, I will move away from the Foursquare API limitations and switch completely to the Open Street Maps API where it is possible to query multipolygon areas for anything with no limitations. That way the whole suburb area can be queried for features instead of a fixed radius around a node. Also, there is a wealth of information that one can add to the suburb character features that is not even available at Foursquare, like number of trees, area of the suburb covered by greenery, public transporation density, etc. Although such an analysis will take me beyond the scope of this project, I still consider it an interesting project to work on in the future.
The question asked by my friends was a very specific one. I was tempted to use machine-learning methods like K-means clustering in order to identify groups of suburbs across the cities. However, such an analysis would not have answered the question that my friends asked. If anything, it would have left them more confused having to choose by themselves among the suburbs that co-clustered with a Cologne suburb. I chose a way of analysis that was tailored to the exact question asked: Can you make a map of the Cologne to Berlin suburbs for us?
A map of the Cologne to Berlin suburbs was made by looking at very basic information like food venues, shops and various services. Suburbs of Cologne were mapped to the two closest suburbs in Berlin, listing also the distance in Euclidean feature space as well as information about the top similar and top dissimilar features involved. The results were quite satisfactory. This analysis can serve as a blueprint for future work that can expand it and improve on the type of features used in order to capture more accurately the character of suburbs.